﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Data;
using System.Text.RegularExpressions;
using System.IO;

namespace newTurtle8
{
   
    public class SqlManager
    {
        public enum CommandType
        {
            Insert,
            Update,
            Delete,
            Select
        }

        #region Data Members
        static private SqlConnection conn;
        private string connStr;
        #endregion


        #region Properties
        static public SqlConnection Connection
        { get { return conn; } }
        #endregion

        public SqlManager()
        {
            initConnection(false);
        }

        #region Methods

        internal void initConnection(bool isFirstCall)
        {
            string serverName = CompanyManager.getValueFromConfigFile("DBServer");
            
            if (isFirstCall)
                connStr = "Server=" + serverName + @";Database=master;Trusted_Connection=True;Connection Timeout = 30;";
            else connStr = "Server=" + serverName + @";Database=CompanyManager;Trusted_Connection=True;Connection Timeout = 30;";

            conn = new SqlConnection(connStr);
        }
        #endregion

        #region SQL Methods

        static public DataTable selectSqlQueryDataTable(SqlCommand sqlCmd)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter adapter = SqlManager.SqlDataAdapterFactory(sqlCmd);
            adapter.Fill(dt);

            return dt;
        }

        static public Int16 executeSqlScalar(SqlCommand sqlCmd)
        {
            Int16 val;
            
            sqlCmd.Connection.Open();
            val = Convert.ToInt16(sqlCmd.ExecuteScalar());
            sqlCmd.Connection.Close();

            return val;
        }

        static public void updateSqlNonQuery(SqlCommand updateCmd, DataTable dt)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            da.UpdateCommand = updateCmd;
            da.Update(dt);
        }

        static public void executeNonQuery(SqlCommand sqlCmd)
        {
            try
            {
                conn.Open();
                sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                
            }
            finally
            {
                conn.Close();
            }
        }
        

        static public SqlCommand SqlCommandFactory(string cmdText)
        {
            return new SqlCommand(cmdText, conn);
        }

        static public SqlCommand SqlCommandFactory(FileInfo fi)
        {
            return new SqlCommand(getSqlStringFromFile(@"\" + fi.ToString()), conn);
        }

        static public SqlDataAdapter SqlDataAdapterFactory(string cmdText)
        {
            return new SqlDataAdapter(new SqlCommand(cmdText,conn));
        }

        static public SqlDataAdapter SqlDataAdapterFactory(SqlCommand sqlCmd)
        {
            return new SqlDataAdapter(sqlCmd);
        }
        static public SqlDataReader getSqlDataReader(SqlCommand selectCmd)
        {
            conn.Open();
            return selectCmd.ExecuteReader();
        }
        public static SqlParameter createSqlParameter(string paramName, SqlDbType sdt, DataRowVersion drVersion, string columnStr)
        {
            SqlParameter sqlParameter = new SqlParameter(paramName, sdt);
            sqlParameter.SourceVersion = drVersion;
            sqlParameter.SourceColumn = columnStr;

            return sqlParameter;
        }

        static public ICollection<string> parseSqlQueryParameters(string cmd)
        {
            ICollection<string> list = new List<string>();

            while (cmd.Contains('@'))
            {
                //truncating the unnecessary prefix of "@......."
                cmd = cmd.Substring(cmd.IndexOf('@') + 1);

                //finds the first occurence  of a non alphabetic symbol
                Match m = RegEx.regExNotAlphaBetaDigit.Match(cmd);
                int i = m.Index;
                if (i != 0)
                    list.Add(cmd.Substring(0, i).Trim());
                else list.Add(cmd.Trim());
            }
            return list;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="filename"></param>
        /// <returns>the filename as a string</returns>
        static public SqlCommand getSqlCommandFromFile(string filename)
        {
            TextReader tr = new StreamReader(Application.StartupPath + @"\sql" + filename, Encoding.GetEncoding("windows-1255"));

            // reads the whole file and returns it as a string.
            string cmdStr = tr.ReadToEnd();

            // close the stream.
            tr.Close();

            return SqlCommandFactory(cmdStr);
        }

        static public string getSqlStringFromFile(string filename)
        {
            TextReader tr = new StreamReader(Application.StartupPath + @"\sql" + filename, Encoding.GetEncoding("windows-1255"));

            // reads the whole file and returns it as a string.
            string cmdStr = tr.ReadToEnd();

            // close the stream.
            tr.Close();

            return cmdStr;
        }

        static public T cast<T>(DataRow row, string fieldName)
        {
            if (!DBNull.Value.Equals(row[fieldName]))
                return (T)row[fieldName];
            return default(T);

        }

        #endregion
    }
}
    
